// 数据库优化配置 - 高性能连接池和缓存策略
const pg = require('pg');
const redis = require('redis');
const { MongoClient } = require('mongodb');
const config = require('../../config/config.js');

const { Pool } = pg;

// ==================== 连接池优化配置 ====================

// PostgreSQL连接池优化配置
const postgresPool = new Pool({
  host: config.database.postgresql.host,
  port: config.database.postgresql.port,
  database: config.database.postgresql.name,
  user: config.database.postgresql.user,
  password: config.database.postgresql.password,
  
  // 连接池大小优化
  max: config.database.postgresql.maxConnections || 50, // 增加最大连接数
  min: config.database.postgresql.minConnections || 5,  // 最小连接数
  
  // 超时配置优化
  idleTimeoutMillis: config.database.postgresql.idleTimeout || 30000,
  connectionTimeoutMillis: config.database.postgresql.connectionTimeout || 5000,
  
  // 连接生命周期管理
  maxUses: 7500, // 单个连接最大使用次数
  allowExitOnIdle: true,
  
  // 连接验证
  application_name: 'bike-wholesale-app',
  
  // 连接重试策略
  retry: {
    max: 5,
    delay: 2000,
    factor: 2
  }
});

// Redis连接池优化配置
const redisClient = redis.createClient({
  socket: {
    host: config.database.redis.host,
    port: config.database.redis.port,
    reconnectStrategy: (retries) => {
      if (retries > 10) {
        console.log('Redis重连次数过多，停止重连');
        return new Error('Redis重连失败');
      }
      return Math.min(retries * 100, 3000);
    }
  },
  password: config.database.redis.password,
  database: config.database.redis.db,
  
  // 连接池配置
  pingInterval: 60000, // 每分钟ping一次保持连接
  
  // 序列化优化
  serialization: {
    encoder: (value) => JSON.stringify(value),
    decoder: (value) => JSON.parse(value)
  }
});

// MongoDB连接池优化配置
let mongoClient = null;
let mongoDb = null;

async function connectMongoDB() {
  try {
    mongoClient = new MongoClient(config.database.mongodb.uri, {
      // 连接池优化
      maxPoolSize: config.database.mongodb.poolSize || 50,
      minPoolSize: config.database.mongodb.minPoolSize || 5,
      maxIdleTimeMS: 30000,
      
      // 连接超时优化
      connectTimeoutMS: 10000,
      socketTimeoutMS: 45000,
      serverSelectionTimeoutMS: 10000,
      
      // 读写偏好
      readPreference: 'primaryPreferred',
      
      // 压缩
      compressors: ['zlib'],
      zlibCompressionLevel: 6
    });
    
    await mongoClient.connect();
    mongoDb = mongoClient.db(config.database.mongodb.db);
    console.log('✅ MongoDB连接成功');
    
    // 创建优化索引
    await createOptimizedIndexes();
    
  } catch (error) {
    console.error('❌ MongoDB连接失败:', error.message);
    throw error;
  }
}

// ==================== 索引优化 ====================

async function createOptimizedIndexes() {
  try {
    // 产品评价索引优化
    await mongoDb.collection('product_reviews').createIndexes([
      { key: { product_id: 1, created_at: -1 } }, // 按产品和时间排序
      { key: { rating: -1, helpful_votes: -1 } },  // 按评分和有用投票排序
      { key: { user_id: 1, created_at: -1 } }     // 按用户和时间排序
    ]);
    
    // 社区讨论索引优化
    await mongoDb.collection('community_posts').createIndexes([
      { key: { category: 1, created_at: -1 } },
      { key: { tags: 1, created_at: -1 } },
      { key: { author_id: 1, status: 1 } },
      { key: { title: 'text', content: 'text' } } // 全文搜索索引
    ]);
    
    // 用户行为索引优化
    await mongoDb.collection('user_behavior').createIndexes([
      { key: { user_id: 1, action_type: 1, timestamp: -1 } },
      { key: { session_id: 1, timestamp: -1 } },
      { key: { product_id: 1, action_type: 1 } },
      { key: { timestamp: 1 }, expireAfterSeconds: 2592000 } // 30天自动过期
    ]);
    
    console.log('✅ MongoDB优化索引创建完成');
  } catch (error) {
    console.warn('⚠️ MongoDB索引创建失败:', error.message);
  }
}

// ==================== 查询优化工具 ====================

// 查询性能监控
class QueryMonitor {
  constructor() {
    this.slowQueryThreshold = 1000; // 1秒
    this.queryStats = new Map();
  }
  
  async monitorQuery(queryName, queryFn) {
    const startTime = Date.now();
    try {
      const result = await queryFn();
      const duration = Date.now() - startTime;
      
      if (duration > this.slowQueryThreshold) {
        console.warn(`⚠️ 慢查询警告: ${queryName} 耗时 ${duration}ms`);
      }
      
      // 记录统计信息
      this.recordQueryStats(queryName, duration, true);
      
      return result;
    } catch (error) {
      const duration = Date.now() - startTime;
      this.recordQueryStats(queryName, duration, false);
      throw error;
    }
  }
  
  recordQueryStats(queryName, duration, success) {
    const stats = this.queryStats.get(queryName) || {
      count: 0,
      totalTime: 0,
      errors: 0,
      lastExecuted: null
    };
    
    stats.count++;
    stats.totalTime += duration;
    stats.lastExecuted = new Date();
    if (!success) stats.errors++;
    
    this.queryStats.set(queryName, stats);
  }
  
  getQueryStats() {
    return Object.fromEntries(this.queryStats.entries());
  }
}

// ==================== 缓存策略优化 ====================

class CacheManager {
  constructor() {
    this.cachePrefix = config.database.redis.keyPrefix || 'bike:';
    this.defaultTTL = config.performance.cache.ttl || 3600;
    this.cacheHits = 0;
    this.cacheMisses = 0;
  }
  
  // 智能缓存键生成
  generateCacheKey(type, id, params = {}) {
    const paramString = Object.keys(params)
      .sort()
      .map(key => `${key}=${params[key]}`)
      .join('&');
    
    return `${this.cachePrefix}${type}:${id}${paramString ? ':' + paramString : ''}`;
  }
  
  // 批量缓存操作
  async multiSet(cacheItems) {
    const pipeline = redisClient.multi();
    
    for (const item of cacheItems) {
      const { key, value, ttl = this.defaultTTL } = item;
      pipeline.setEx(key, ttl, JSON.stringify(value));
    }
    
    return await pipeline.exec();
  }
  
  async multiGet(keys) {
    const pipeline = redisClient.multi();
    
    for (const key of keys) {
      pipeline.get(key);
    }
    
    const results = await pipeline.exec();
    return results.map(result => 
      result ? JSON.parse(result) : null
    );
  }
  
  // 缓存预热
  async warmUpCache(cacheData) {
    console.log('🔥 开始缓存预热...');
    
    for (const [key, value] of Object.entries(cacheData)) {
      await this.setCache(key, value, 3600); // 预热缓存1小时
    }
    
    console.log('✅ 缓存预热完成');
  }
  
  // 缓存统计
  getCacheStats() {
    const hitRate = this.cacheHits + this.cacheMisses > 0 
      ? (this.cacheHits / (this.cacheHits + this.cacheMisses)) * 100 
      : 0;
    
    return {
      hits: this.cacheHits,
      misses: this.cacheMisses,
      hitRate: hitRate.toFixed(2) + '%',
      total: this.cacheHits + this.cacheMisses
    };
  }
}

// ==================== 连接池监控 ====================

class ConnectionPoolMonitor {
  constructor() {
    this.monitorInterval = null;
    this.statsHistory = [];
  }
  
  startMonitoring(intervalMs = 60000) { // 每分钟监控一次
    this.monitorInterval = setInterval(() => {
      this.recordPoolStats();
    }, intervalMs);
  }
  
  stopMonitoring() {
    if (this.monitorInterval) {
      clearInterval(this.monitorInterval);
      this.monitorInterval = null;
    }
  }
  
  async recordPoolStats() {
    const stats = {
      timestamp: new Date(),
      postgres: {
        totalCount: postgresPool.totalCount,
        idleCount: postgresPool.idleCount,
        waitingCount: postgresPool.waitingCount
      },
      redis: {
        connected: redisClient.isOpen
      }
    };
    
    this.statsHistory.push(stats);
    
    // 保留最近100条记录
    if (this.statsHistory.length > 100) {
      this.statsHistory = this.statsHistory.slice(-100);
    }
    
    // 检查连接池健康状态
    this.checkPoolHealth(stats);
  }
  
  checkPoolHealth(stats) {
    const { postgres } = stats;
    
    // 检查PostgreSQL连接池
    if (postgres.waitingCount > 10) {
      console.warn('⚠️ PostgreSQL连接池等待连接过多:', postgres.waitingCount);
    }
    
    if (postgres.idleCount === 0 && postgres.totalCount > 0) {
      console.warn('⚠️ PostgreSQL连接池无空闲连接');
    }
  }
  
  getPoolStats() {
    return {
      current: this.statsHistory[this.statsHistory.length - 1],
      history: this.statsHistory
    };
  }
}

// ==================== 数据库优化工具 ====================

// 查询优化建议
class QueryOptimizer {
  static analyzeQuery(query, params, executionPlan) {
    const suggestions = [];
    
    // 检查是否缺少索引
    if (executionPlan && executionPlan.plan && executionPlan.plan['Node Type'] === 'Seq Scan') {
      suggestions.push('建议添加索引以提高查询性能');
    }
    
    // 检查查询复杂度
    if (query.includes('SELECT *')) {
      suggestions.push('建议指定具体字段而不是使用SELECT *');
    }
    
    // 检查JOIN优化
    if (query.includes('JOIN') && !query.includes('WHERE')) {
      suggestions.push('JOIN查询建议添加WHERE条件限制结果集');
    }
    
    return suggestions;
  }
  
  // 分页优化
  static optimizePagination(query, page, pageSize) {
    const offset = (page - 1) * pageSize;
    
    // 对于大数据集，使用游标分页
    if (page > 100) {
      return {
        optimized: true,
        suggestion: '考虑使用基于游标的分页替代OFFSET分页',
        cursorField: 'id'
      };
    }
    
    return { optimized: false, offset, limit: pageSize };
  }
}

// ==================== 导出优化后的接口 ====================

const queryMonitor = new QueryMonitor();
const cacheManager = new CacheManager();
const poolMonitor = new ConnectionPoolMonitor();

// 启动连接池监控
poolMonitor.startMonitoring();

// 数据库配置对象
const databaseConfig = {
  // 数据库连接
  postgresPool: postgresPool,
  redisClient: redisClient,
  mongoDb: mongoDb,
  
  // 优化工具
  queryMonitor: queryMonitor,
  cacheManager: cacheManager,
  poolMonitor: poolMonitor,
  QueryOptimizer: QueryOptimizer,
  
  // 连接管理
  connectMongoDB: connectMongoDB,
  
  // 健康检查
  async healthCheck() {
    const checks = await Promise.allSettled([
      postgresPool.query('SELECT 1').then(() => 'healthy').catch(() => 'unhealthy'),
      redisClient.ping().then(() => 'healthy').catch(() => 'unhealthy'),
      mongoDb ? mongoDb.command({ ping: 1 }).then(() => 'healthy').catch(() => 'unhealthy') : 'not_configured'
    ]);
    
    const results = checks.map((check) => 
      check.status === 'fulfilled' ? check.value : 'error'
    );
    
    return {
      status: results.every(r => r === 'healthy') ? 'healthy' : 'degraded',
      services: {
        postgresql: results[0],
        redis: results[1],
        mongodb: results[2]
      },
      cacheStats: cacheManager.getCacheStats(),
      poolStats: poolMonitor.getPoolStats(),
      timestamp: new Date().toISOString()
    };
  },
  
  // 优雅关闭
  async gracefulShutdown() {
    console.log('🛑 正在关闭优化数据库连接...');
    
    // 停止监控
    poolMonitor.stopMonitoring();
    
    try {
      // 关闭Redis连接
      if (redisClient && redisClient.isOpen) {
        await redisClient.quit();
        console.log('✅ Redis连接已关闭');
      }
      
      // 关闭PostgreSQL连接池
      if (postgresPool) {
        await postgresPool.end();
        console.log('✅ PostgreSQL连接池已关闭');
      }
      
      // 关闭MongoDB连接
      if (mongoClient) {
        await mongoClient.close();
        console.log('✅ MongoDB连接已关闭');
      }
      
      console.log('✅ 所有数据库连接已优雅关闭');
    } catch (error) {
      console.error('❌ 关闭数据库连接时出错:', error);
    }
  }
}

module.exports = databaseConfig;